const db = require('../db/index')

//请求填写问卷的处理函数
exports.postQuestionnaire = (req, res) => {
	const sql = 'insert into questionnaire set ?'
	db.query(sql, req.body, (err, results) => {
		// 1. 执行 SQL 语句失败
		if (err) return res.cc(err)

		// 2. 执行 SQL 语句成功，但是影响的数据条数不等于 1
		if (results.affectedRows !== 1) return res.cc('填写问卷数据失败！')

		//3.成功
		res.cc("填写问卷数据成功！", 0)
	})
}
//获取问卷数据的处理函数
exports.getQuestionnaire = (req, res) => {
	const sql = `select 
	COUNT(CASE WHEN Age = 'a' THEN 1 END) AS Age_below_20,
	    COUNT(CASE WHEN Age = 'b' THEN 1 END) AS Age_between_20_29,
	    COUNT(CASE WHEN Age = 'c' THEN 1 END) AS Age_between_30_39,
	    COUNT(CASE WHEN Age = 'd' THEN 1 END) AS Age_between_40_49,
		COUNT(CASE WHEN Age = 'e' THEN 1 END) AS Age_above_50,
	
		AVG(liuchang) as liuchang2,
		AVG(duoyang) as duoyang2,
		AVG(qihe) as qihe2,
		AVG(bianjie) as bianjie2,
		AVG(zongti) as zongti2,
		COUNT(CASE WHEN date1 = 'Jan' THEN 1 END) AS date1_Jan,
		COUNT(CASE WHEN date1 = 'Feb' THEN 1 END) AS date1_Feb,
		COUNT(CASE WHEN date1 = 'Mar' THEN 1 END) AS date1_Mar,
		COUNT(CASE WHEN date1 = 'Apr' THEN 1 END) AS date1_Apr,
		COUNT(CASE WHEN date1 = 'May' THEN 1 END) AS date1_May,
		COUNT(CASE WHEN date1 = 'Jun' THEN 1 END) AS date1_Jun,
		COUNT(CASE WHEN date1 = 'Jul' THEN 1 END) AS date1_Jul,
		COUNT(CASE WHEN date1 = 'Aug' THEN 1 END) AS date1_Aug,
		COUNT(CASE WHEN date1 = 'Sep' THEN 1 END) AS date1_Sep,
		COUNT(CASE WHEN date1 = 'Oct' THEN 1 END) AS date1_Oct,
		COUNT(CASE WHEN date1 = 'Nov' THEN 1 END) AS date1_Nov,
		COUNT(CASE WHEN date1 = 'Dec' THEN 1 END) AS date1_Dec,
		COUNT(CASE WHEN delivery = 1 THEN 1 END) AS delivery_1,
		COUNT(CASE WHEN delivery = 0 THEN 1 END) AS delivery_0,
		COUNT(CASE WHEN gender = 1 THEN 1 END) AS gender_1,
		COUNT(CASE WHEN gender = 0 THEN 1 END) AS gender_0,
		COUNT(CASE WHEN timeOfUse = '1' THEN 1 END) AS timeOfUse_below_30,
		    COUNT(CASE WHEN timeOfUse = '2' THEN 1 END) AS timeOfUse_between_30_60,
		    COUNT(CASE WHEN timeOfUse = '3' THEN 1 END) AS timeOfUse_between_60_90,
		    COUNT(CASE WHEN timeOfUse = '4' THEN 1 END) AS timeOfUse_between_90_120,
			COUNT(CASE WHEN timeOfUse = '5' THEN 1 END) AS timeOfUse_between_120_150,
			COUNT(CASE WHEN timeOfUse = '6' THEN 1 END) AS timeOfUse_between_150_180,
			COUNT(CASE WHEN timeOfUse = '7' THEN 1 END) AS timeOfUse_above_180,
			COUNT(liuxing) as liuxing,
			COUNT(yaogun) as yaogun,
			COUNT(jueshi) as jueshi,
			COUNT(gudian) as gudian,
			COUNT(xiha) as xiha,
			COUNT(dianzi) as dianzi,
			COUNT(xiangcun) as xiangcun,
			COUNT(minzu) as minzu,
			COUNT(jinshu) as jinshu,
			COUNT(yaobai) as yaobai
			from questionnaire
	`
	db.query(sql, (err, results) => {
		// 1. 执行 SQL 语句失败
		if (err) return res.cc(err)

		let SqlObj = {
			Age: {
				Age_below_20: results[0].Age_below_20,
				Age_between_20_29: results[0].Age_between_20_29,
				Age_between_30_39: results[0].Age_between_30_39,
				Age_between_40_49: results[0].Age_between_40_49,
				Age_above_50: results[0].Age_above_50
			},
	
			FiveCommentAVG: {
				liuchangAVG: results[0].liuchang2,
				duoyangAVG: results[0].duoyang2,
				qiheAVG: results[0].qihe2,
				bianjieAVG: results[0].bianjie2,
				zongtiAVG: results[0].zongti2,
			},
			month: {

				Jan: results[0].date1_Jan,
				Feb: results[0].date1_Feb,
				Mar: results[0].date1_Mar,
				Apr: results[0].date1_Apr,
				May: results[0].date1_May,
				Jun: results[0].date1_Jun,
				Jul: results[0].date1_Jul,
				Aug: results[0].date1_Aug,
				Sep: results[0].date1_Sep,
				Oct: results[0].date1_Oct,
				Nov: results[0].date1_Nov,
				Dec: results[0].date1_Dec

			},
			delivery: {

				delivery_0: results[0].delivery_0,
				delivery_1: results[0].delivery_1,
			},
			gender: {
				gender_0: results[0].gender_0,
				gender_1: results[0].gender_1,
			},
			timeOfUse: {
				timeOfUse_below_30: results[0].timeOfUse_below_30,
				timeOfUse_between_30_60: results[0].timeOfUse_between_30_60,
				timeOfUse_between_60_90: results[0].timeOfUse_between_60_90,
				timeOfUse_between_90_120: results[0].timeOfUse_between_90_120,
				timeOfUse_between_120_150: results[0].timeOfUse_between_120_150,
				timeOfUse_between_150_180: results[0].timeOfUse_between_150_180,
				timeOfUse_above_180: results[0].timeOfUse_above_180,
			},
			MusicType: {
				pop: results[0].liuxing,
				rock: results[0].yaogun,
				jazz: results[0].jueshi,
				classical: results[0].gudian,
				hipHop: results[0].xiha,
				electronic: results[0].dianzi,
				country: results[0].xiangcun,
				folk: results[0].minzu,
				metal: results[0].jinshu,
				swing: results[0].yaobai,
			}
		}
		// // 2. 执行 SQL 语句成功，但是影响的数据条数不等于 1
		// if (results.affectedRows !== 1) return res.cc('填写问卷数据失败！')

		//3.成功
		res.send({
			status: 0,
			message: "获取问卷信息成功！",
			data: SqlObj
		})
	})
}

//获取注册用户总数以及问卷填写总数的处理函数
exports.getUserAndQuestionnaireTotal = async (req, res) => {

	const userPromise = new Promise((resolve, reject) => {
		db.query('select count(*) as user_total from melody_users', (err, results) => {
			if (err) return reject(err);
			if (results.length !== 1) return reject('查询注册用户总数失败！');
			resolve(results[0].user_total);
		});
	});
	const questionnairePromise = new Promise((resolve, reject) => {
		db.query('select count(*) as questionnaire_total from questionnaire', (err, results) => {
			if (err) return reject(err);
			if (results.length !== 1) return reject('查询问卷反馈总数失败！');
			resolve(results[0].questionnaire_total);
		});
	});

	try {
		//拿到成功的promise对象返回的内容
		const [userTotal, questionnaireTotal] = await Promise.all([userPromise, questionnairePromise]);
		const data = {
			user_total: userTotal,
			questionnaire_total: questionnaireTotal
		};
		res.send({
			status: 0,
			message: '获取用户总数和问卷总数成功！',
			data
		});
	} catch (error) {
		res.cc(error);
	}
};